/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package vn.edu.vttu;

import java.net.InetAddress;
import java.rmi.RemoteException;
import java.rmi.registry.LocateRegistry;
import java.rmi.registry.Registry;
import java.rmi.server.UnicastRemoteObject;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
import javax.swing.JTable;
import javax.swing.table.AbstractTableModel;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;
import vn.edu.vttu.model.NhanVien;
import net.proteanit.sql.DbUtils;
import vn.edu.vttu.model.DichVu;
import vn.edu.vttu.model.KhachHang;
import vn.edu.vttu.model.Loaiphong;
import vn.edu.vttu.model.Phong;

/**
 *
 * @author Administrator
 */
public class Server_QuaLyKhachSan extends UnicastRemoteObject implements RMI_Interface {

    public Server_QuaLyKhachSan() throws RemoteException {
        super();
    }
    private ResultSet rs;
    private PreparedStatement psr;

    private Connection conn() {
        Connection cn = null;
        readInfo read = new readInfo();
        Server sv = new Server();
        try {
            String db=databaseinfo.getDb();
            String usr=databaseinfo.getUser();
            String pas=databaseinfo.getPass();
            String ip = databaseinfo.getIp();
            int port=databaseinfo.getPort();
            Class.forName("com.mysql.jdbc.Driver");
            String database = "jdbc:mysql://" + ip + ":" + port + "/" +db;
            String username = usr;
            String password = pas;
            cn = DriverManager.getConnection(database, username, password);
        } catch (Exception e) {
        }
        return cn;
    }

    public void runServer(){
        /*
       Server sv = new Server();
        try {
            readInfo read = new readInfo();
            Server_QuaLyKhachSan rmiServer = new Server_QuaLyKhachSan();
            // Đăng kí RmiServer lên Rmi Registry 
            LocateRegistry.createRegistry(Integer.parseInt(read.getInfo("portserver")));
            Registry registry = LocateRegistry.getRegistry(Integer.parseInt(read.getInfo("portserver")));
            registry.rebind("ServerKhachSan", rmiServer);
            
            sv.setVisible(true);
            sv.txtArea.append("Server Started...\nCổng: "+read.getInfo("portserver")+"\nTên Server: ServerKhachSan\nIP: "+InetAddress.getLocalHost().getHostAddress());            
            //System.out.println("Server Started...");
        } catch (Exception e) {
            sv.txtArea.append("Không thể khởi động Server");

        }
                */
    }
    public TableModel login(String _user, String _pass) throws RemoteException {
        TableModel tb = null;
        try {
            Statement state = conn().createStatement();
            String sql = "select nv.idNV,hoten,tk.username, tk.password, cv.idChucvu from tbl_nhanvien nv, tbl_chucvu cv, tbl_taikhoan tk where nv.idNV=tk.idNV and nv.chucvu=cv.idChucvu and tk.username='" + _user + "' and tk.password='" + _pass + "' and tk.daxoa=0";
            ResultSet x = state.executeQuery(sql);
            tb = DbUtils.resultSetToTableModel(x);
        } catch (Exception e) {
            //login = false;
            e.printStackTrace();
        }
        return tb;

    }

    public static void main(String[] arrgs) {
        try{
        Server_QuaLyKhachSan server = new Server_QuaLyKhachSan();
        server.runServer();
        }catch(Exception ex){
            
        }
            
    }

    @Override
    public Vector selectNhanVien() throws RemoteException {
        Vector result = new Vector();
        try {
            //1 Kết nối đến csdl mysql
            //-- Nap Driver

            Statement state = conn().createStatement();
            String sql = "Select idNV,hoten from tbl_nhanvien where daxoa=0";
            java.sql.ResultSet x = state.executeQuery(sql);
            while (x.next()) {
                NhanVien nhanvien = new NhanVien(x.getInt(1), x.getString(2));
                result.add(nhanvien);
            }
        } catch (Exception e) {

            e.printStackTrace();
        }
        return result;
    }

    @Override
    public boolean insertAccount(String user, String pass, int idNV) throws RemoteException {
        boolean insert = false;
        try {
            Statement state = conn().createStatement();
            String sql = "insert into tbl_taikhoan(username,password,idNV) values('" + user + "','" + pass + "','" + idNV + "')";
            int x = state.executeUpdate(sql);
            if (x == 1) {
                insert = true;
            } else {
                insert = false;
            }
        } catch (Exception e) {
            insert = false;
        }
        return insert;
    }

    @Override
    public boolean insertNhanVien(String hoten, String ngaysinh, int gioitinh, String diachi, String sdt, String email, String cmnd, String ngaylam, String ghichu) throws RemoteException {
        boolean inserNV = false;
        try {
            Statement state = conn().createStatement();
            String sql = "INSERT INTO tbl_nhanvien(hoten,ngaysinh,gioitinh,diachi,sdt,email,cmnd,ngayvaolam,ghichu)\n"
                    + "VALUES ('" + hoten + "','" + ngaysinh + "','" + gioitinh + "','" + diachi + "','" + sdt + "','" + email + "','" + cmnd + "','" + ngaylam + "','" + ghichu + "')";
            int x = state.executeUpdate(sql);
            if (x == 1) {
                inserNV = true;
            } else {
                inserNV = false;
            }
        } catch (Exception e) {
            inserNV = false;
        }
        return inserNV;
    }

    @Override
    public boolean UpdateNhanVien(int manv, String hoten, String ngaysinh, int gioitinh, String diachi, String sdt, String email, String cmnd, String ngaylam, String ghichu) throws RemoteException {
        boolean updateNV = false;
        try {
            Statement state = conn().createStatement();
            String sql = "UPDATE tbl_nhanvien SET hoten='" + hoten + "',ngaysinh='" + ngaysinh + "',gioitinh='" + gioitinh + "',diachi='" + diachi + "',sdt='" + sdt + "',email='" + email + "',cmnd='" + cmnd + "',ngayvaolam='" + ngaylam + "',ghichu='" + ghichu + "' where idNV='" + manv + "'";
            int x = state.executeUpdate(sql);
            if (x == 1) {
                updateNV = true;
            } else {
                updateNV = false;
            }
        } catch (Exception e) {
            updateNV = false;
        }
        return updateNV;
    }

    @Override
    public TableModel loadDataNhanVien() throws RemoteException {
        TableModel rsTB = null;
        try {
            String sql = "SELECT idNV AS 'Mã NV',hoten AS 'Họ Tên',ngaysinh as 'Ngày Sinh',(CASE gioitinh WHEN TRUE THEN 'Nam' ELSE 'Nu' END) AS 'Giới Tính',diachi AS 'Địa Chỉ',sdt as 'SĐT',email as 'Email',cmnd as 'CMND',ngayvaolam as 'Ngày Làm',ghichu as 'Ghi Chú' FROM tbl_nhanvien where daxoa=0";
            Statement state = conn().createStatement();
            rs = state.executeQuery(sql);// chứa ds các record
            rsTB = DbUtils.resultSetToTableModel(rs);// return table model
        } catch (Exception ex) {

        }
        return rsTB;
    }

    @Override
    public AbstractTableModel timKiem(String key) throws RemoteException {
        TableModel resultSetToTableModel = null;
        try {
            String sql = "SELECT idNV AS 'Mã NV',"
                    + "hoten AS 'Họ Tên',"
                    + "ngaysinh as 'Ngày Sinh',"
                    + "(CASE gioitinh WHEN TRUE THEN 'Nam' ELSE 'Nu' END) AS 'Giới Tính',"
                    + "diachi AS 'Địa Chỉ',"
                    + "sdt as 'SĐT',"
                    + "email as 'Email',"
                    + "cmnd as 'CMND',"
                    + "ngayvaolam as 'Ngày Làm',"
                    + "ghichu as 'Ghi Chú' "
                    + "FROM tbl_nhanvien "
                    + "where hoten LIKE '%" + key + "%' or diachi LIKE '%" + key + "%' and daxoa=0";
            psr = conn().prepareStatement(sql);
            rs = psr.executeQuery();
            resultSetToTableModel = DbUtils.resultSetToTableModel(rs);
        } catch (Exception ex) {

        }
        return (AbstractTableModel) resultSetToTableModel;
    }

    @Override
    public boolean DeleteNhanVien(int manv) throws RemoteException {
        boolean del = false;
        try {
            Statement state = conn().createStatement();
            String sql = "update tbl_nhanvien set daxoa=1 where idNV=" + manv;
            int x = state.executeUpdate(sql);
            if (x == 1) {
                del = true;
            } else {
                del = false;
            }
        } catch (Exception e) {
            del = false;
        }
        return del;
    }

    @Override
    public TableModel loadKh() throws RemoteException {
        TableModel tb = null;
        ResultSet rs;
        Statement state;
        try {
            String sql = "select idKhachhang as 'ID', hoten as 'Họ Tên',diachi as 'Địa Chỉ', sdt as 'SĐT', cmnd as 'CMND',ngaysinh as 'Ngày Sinh', (CASE gioitinh WHEN TRUE THEN 'Nam' ELSE 'Nữ' END) AS 'Giới Tính' from tbl_khachhang where daxoa=0";
            state = conn().createStatement();
            rs = state.executeQuery(sql);
            tb = DbUtils.resultSetToTableModel(rs);
        } catch (Exception e) {
        }
        return tb;
    }

    @Override
    public TableModel loadDataTaiKhoan() throws RemoteException {
        TableModel tb = null;
        ResultSet rs;
        Statement state;
        try {
            String sql = "select id as 'Mã Số', tk.idNV as 'Mã Nhân Viên',hoten as 'Nhân Viên', username as 'Tài Khoản', password as 'Password', ngaytao as 'Ngày Tạo', (CASE tk.daXoa WHEN TRUE THEN 'No' ELSE 'Yes' END) as 'Active' from tbl_taikhoan as tk, tbl_nhanvien as nv where tk.idNV=nv.idNV order by id desc";
            state = conn().createStatement();
            rs = state.executeQuery(sql);
            tb = DbUtils.resultSetToTableModel(rs);
        } catch (Exception e) {
        }
        return tb;
    }

    @Override
    public boolean insertTaiKhoan(String username, String pass, int idNV, int daxoa) throws RemoteException {
        boolean flag = false;
        try {
            Statement state = conn().createStatement();
            String sql = "insert into tbl_taikhoan(username,password,idNV,ngaytao,daXoa) values('" + username + "','" + pass + "','" + idNV + "',now(),'"+daxoa+"')";
            int x = state.executeUpdate(sql);
            if (x == 1) {
                flag = true;
            } else {
                flag = false;
            }
        } catch (Exception e) {
        }
        return flag;
    }

    @Override
    public boolean updateTaiKhoan(int id, String username, String pass, int idNV, int daxoa) throws RemoteException {
        boolean flag = false;
        try {
            Statement state = conn().createStatement();
           String sql = "update tbl_taikhoan set username='" + username + "',password='" + pass + "',idNV='" + idNV + "',daXoa='"+daxoa+"' where id='" + id + "'";
            int x = state.executeUpdate(sql);
            if (x == 1) {
                flag = true;
            } else {
                flag = false;
            }
        } catch (Exception e) {
        }
        return flag;
    }

    @Override
    public TableModel timKiemTaiKhoan(String key) throws RemoteException {
        TableModel tb = null;
        ResultSet rs;
        Statement state;
        try {
            String sql = "select id as 'Mã Số', tk.idNV as 'Mã Nhân Viên',hoten as 'Nhân Viên', username as 'Tài Khoản', password as 'Password', ngaytao as 'Ngày Tạo', (CASE tk.daXoa WHEN TRUE THEN 'No' ELSE 'Yes' END) as 'Active' from tbl_taikhoan as tk, tbl_nhanvien as nv where tk.idNV=nv.idNV and tk.daXoa=0 and (username LIKE '%" + key + "%' or hoten LIKE '%" + key + "%') order by id desc";
            state = conn().createStatement();
            rs = state.executeQuery(sql);
            tb = DbUtils.resultSetToTableModel(rs);
        } catch (Exception e) {
        }
        return tb;
    }

    @Override
    public boolean insertKhachHang(String hoten, String diachi, String sdt, String cmnd, String ngaysinh, int gioitinh) throws RemoteException {
        boolean them = false;
        try {
            Statement state = conn().createStatement();
            String sql = "Insert into tbl_khachhang(hoten,diachi,sdt,cmnd,ngaysinh,gioitinh) values('" + hoten + "','" + diachi + "','" + sdt + "','" + cmnd + "','" + ngaysinh + "','" + gioitinh + "')";
            int x = state.executeUpdate(sql);
            if (x == 1) {
                them = true;
            } else {
                them = false;
            }
        } catch (Exception ex) {
            them = false;
        }
        return them;
    }

    @Override
    public boolean UpdateKhachHang(int id, String hoten, String diachi, String sdt, String cmnd, String ngaysinh, int gioitinh) throws RemoteException {
        boolean sua = false;
        try {
            Statement state = conn().createStatement();
            String sql = "update tbl_khachhang set hoten='" + hoten + "',diachi='" + diachi + "',sdt='" + sdt + "',cmnd='" + cmnd + "',ngaysinh='" + ngaysinh + "',gioitinh='" + gioitinh + "' where idKhachhang='" + id + "'";
            int x = state.executeUpdate(sql);
            if (x == 1) {
                sua = true;
            } else {
                sua = false;
            }
        } catch (Exception ex) {
            sua = false;
        }
        return sua;
    }

    @Override
    public TableModel timKiemKachHang(String key) throws RemoteException {
        TableModel tb = null;
        ResultSet rs;
        Statement state;
        try {
            String sql = "select idKhachhang as 'ID', hoten as 'Họ Tên',diachi as 'Địa Chỉ', sdt as 'SĐT', cmnd as 'CMND',ngaysinh as 'Ngày Sinh', gioitinh as 'Giới Tính' from tbl_khachhang where daxoa=0 and (hoten LIKE '%" + key + "%' or diachi LIKE '%" + key + "%')";
            state = conn().createStatement();
            rs = state.executeQuery(sql);
            tb = DbUtils.resultSetToTableModel(rs);
        } catch (Exception e) {
        }
        return tb;
    }

    @Override
    public boolean DeleteKhachHang(int makh) throws RemoteException {
        boolean sua = false;
        try {
            Statement state = conn().createStatement();
            String sql = "update tbl_khachhang set daxoa=1 where idKhachhang=" + makh;
            int x = state.executeUpdate(sql);
            if (x == 1) {
                sua = true;
            } else {
                sua = false;
            }
        } catch (Exception ex) {
            sua = false;
        }
        return sua;
    }

    @Override
    public TableModel loadPhong() throws RemoteException {
        TableModel tb = null;
        ResultSet rs;
        Statement state;
        try {
            String sql = "SELECT idPhong AS 'Mã phòng',tenPhong AS 'Tên phòng',vitri AS '[Vị trí]' ,tenloai AS 'Loại phòng', dongia AS 'Giá phòng',dvt AS 'Đơn vị tính' FROM tbl_phong AS a,tbl_loaiphong AS b WHERE a.`loaiPhong`=b.`idLoaiphong` AND a.`daxoa`=0 AND b.`daxoa`=0 order by idPhong asc";
            state = conn().createStatement();
            rs = state.executeQuery(sql);
            tb = DbUtils.resultSetToTableModel(rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tb;
    }

    @Override
    public TableModel loadsudung(int idphong) throws RemoteException {
        TableModel tb = null;
        ResultSet rs;
        Statement state;
        try {
            String sql = "SELECT sd.id,dv.tendichvu AS 'Tên DV' ,sd.ngaysudung AS 'Ngày SD', sd.soluong AS 'Số Lượng',dongia AS 'Đơn Giá', (sd.soluong*dongia) AS 'Thành Tiền' FROM `tbl_sudung` sd,tbl_dichvu dv,`tl_thuephong` AS tp WHERE sd.`idDichvu`=dv.`idDichvu` AND tp.`idPhong`=sd.`idPhong` AND tp.`daxoa`=0 AND sd.daxoa=0 AND dathanhtoan=0 and tp.`idPhong`="+idphong+" GROUP BY sd.`id`";
            state = conn().createStatement();
            rs = state.executeQuery(sql);
            tb = DbUtils.resultSetToTableModel(rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tb;
    }

    @Override
    public TableModel LoadTenKH(int idphong) throws RemoteException {
        ResultSet rs;
        Statement state;
        TableModel tb = null;
        String tenkh = null;
        try {
            String sql = "SELECT hoten,sdt,cmnd,gioitinh,ngaynhan,ngaytra FROM `tbl_khachhang` kh, `tl_thuephong` thue WHERE kh.`idKhachhang`=thue.`idKhachhang` AND thue.`idPhong`='" + idphong + "' AND thue.daxoa=0 and kh.`daxoa`=0 and now() between ngaynhan and ngaytra";
            state = conn().createStatement();
            rs = state.executeQuery(sql);
            tb = DbUtils.resultSetToTableModel(rs);
        } catch (Exception e) {
        }
        return tb;
    }

    @Override
    public TableModel kiemtraphongtrong(int idphong) throws RemoteException {
        Statement state;
        TableModel tb = null;
        try {
            String sql = "SELECT count(*)"
                    + "FROM tbl_phong phong LEFT JOIN `tl_thuephong`  thue ON phong.`idPhong`=thue.`idPhong`"
                    + "WHERE NOW() BETWEEN ngaynhan AND ngaytra and thue.daxoa=0 and thue.`idPhong`='" + idphong + "' AND ngaytra IS NOT NULL";
            state = conn().createStatement();
            rs = state.executeQuery(sql);
            tb = DbUtils.resultSetToTableModel(rs);
        } catch (Exception e) {
        }
        return tb;
    }

    @Override
    public Vector selectKhachHang() throws RemoteException {
        Vector result = new Vector();
        try {
            //1 Kết nối đến csdl mysql
            //-- Nap Driver

            Statement state = conn().createStatement();
            String sql = "SELECT idKhachhang, hoten FROM tbl_khachhang WHERE daxoa=0";
            java.sql.ResultSet x = state.executeQuery(sql);
            while (x.next()) {
                KhachHang kh = new KhachHang(x.getInt(1), x.getString(2));
                result.add(kh);     // nhét vào đối tượng Vector result = new Vector();      
            }
        } catch (Exception e) {

            //e.printStackTrace();
        }
        return result;   // đẩy đối tượng Vector result = new Vector(); về client 
    }

    @Override
    public boolean nhanphong(int idphong, int idkh, String ngaydat, String ngaynhan, String ngaytra, int idNV) throws RemoteException {
        boolean trangthai = false;
        try {
            Statement state = conn().createStatement();
            String sql = "INSERT INTO tl_thuephong(idPhong,idKhachhang,ngaydat,ngaynhan,ngaytra,idNV) "
                    + "values('" + idphong + "','" + idkh + "','" + ngaydat + "','" + ngaynhan + "','" + ngaytra + "','" + idNV + "')";
            int x = state.executeUpdate(sql);
            if (x == 1) {
                trangthai = true;
            } else {
                trangthai = false;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            trangthai = false;
        }
        return trangthai;
    }

    @Override
    public Vector selectDichVu() throws RemoteException {
        Vector result = new Vector();
        try {
            //1 Kết nối đến csdl mysql
            //-- Nap Driver

            Statement state = conn().createStatement();
            String sql = "SELECT idDichvu,tendichvu FROM tbl_dichvu WHERE daxoa=0 ORDER BY idDichvu DESC";
            java.sql.ResultSet x = state.executeQuery(sql);
            while (x.next()) {
                DichVu kh = new DichVu(x.getInt(1), x.getString(2));
                result.add(kh);     // nhét vào đối tượng Vector result = new Vector();      
            }
        } catch (Exception e) {

            //e.printStackTrace();
        }
        return result;   // đẩy đối tượng Vector result = new Vector(); về client 
    }

    @Override
    public boolean insertSudungDV(int idphong, int iddv, int soluong) throws RemoteException {
        boolean trangthai = false;
        try {
            Statement state = conn().createStatement();
            String sql = "INSERT INTO tbl_sudung(idPhong,idDichvu,soluong,ngaysudung) values('" + idphong + "','" + iddv + "','" + soluong + "',now())";
            int x = state.executeUpdate(sql);
            if (x == 1) {
                trangthai = true;
            } else {
                trangthai = false;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            trangthai = false;
        }
        return trangthai;
    }

    @Override
    public boolean updateSudungDV(int id, int idDV, int soluong) throws RemoteException {
        boolean trangthai = false;
        try {
            Statement state = conn().createStatement();
            String sql = "UPDATE tbl_sudung SET idDichvu='"+idDV+"',soluong='"+soluong+"',ngaysudung=now() WHERE id='"+id+"'";
            int x = state.executeUpdate(sql);
            if (x == 1) {
                trangthai = true;
            } else {
                trangthai = false;
            }
        } catch (Exception ex) {
            //ex.printStackTrace();
            trangthai = false;
        }
        return trangthai;
    }

    @Override
    public boolean DeleteSuDungDV(int masddv) throws RemoteException {
        boolean trangthai = false;
        try {
            Statement state = conn().createStatement();
            String sql = "update tbl_sudung set daxoa=1 where id='"+masddv+"'";
            int x = state.executeUpdate(sql);
            if (x == 1) {
                trangthai = true;
            } else {
                trangthai = false;
            }
        } catch (Exception ex) {
            //ex.printStackTrace();
            trangthai = false;
        }
        return trangthai;
    }

    @Override
    public TableModel tinhtiendichvu(int idphong) throws RemoteException {
        Statement state;
        TableModel tb = null;
        try {
            String sql = "SELECT SUM(dv.dongia*sd.soluong) FROM tbl_dichvu dv, tbl_sudung sd, `tl_thuephong` tp WHERE sd.`idDichvu`=dv.`idDichvu` AND tp.`idPhong`=sd.`idPhong` AND tp.`daxoa`=0 AND sd.daxoa=0 AND dathanhtoan=0 AND tp.`idPhong`="+idphong;
            state = conn().createStatement();
            rs = state.executeQuery(sql);
            tb = DbUtils.resultSetToTableModel(rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tb;
    }

    @Override
    public TableModel tinhtienphong(int idphong) throws RemoteException {
        Statement state;
        TableModel tb = null;
        try {
            String sql = "SELECT ((YEAR(ngaytra)*12*31+MONTH(ngaytra)*31+DAY(ngaytra))-(YEAR(ngaynhan)*12*31+MONTH(ngaynhan)*31+DAY(ngaynhan)))*dongia AS tienphong FROM `tbl_phong` phong, `tl_thuephong` thue, `tbl_loaiphong` loai WHERE phong.`idPhong`=thue.`idPhong` AND phong.`loaiPhong`=loai.`idLoaiphong` AND thue.`daxoa`=0 AND phong.`daxoa`=0 AND loai.`daxoa`=0 AND thue.`idPhong`="+idphong;
            state = conn().createStatement();
            rs = state.executeQuery(sql);
            tb = DbUtils.resultSetToTableModel(rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tb;
    }

    @Override
    public Vector loadLoaiphong() throws RemoteException {
         Vector result = new Vector();
        try {
            Statement state = conn().createStatement();
            String sql = "SELECT idloaiphong,tenloai FROM tbl_loaiphong";
            java.sql.ResultSet x = state.executeQuery(sql);
            while (x.next()) {
                Loaiphong kh = new Loaiphong(x.getInt(1), x.getString(2));
                result.add(kh);     // nhét vào đối tượng Vector result = new Vector();      
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public boolean insertPhong(String tenPhong, String vitri, int loaiPhong) throws RemoteException {
         boolean flag = false;
        try {
            Statement state = conn().createStatement();
            String sql = "INSERT INTO tbl_phong(`tenPhong`,`loaiPhong`,`vitri`) VALUES ('"+tenPhong+"',"+loaiPhong+",'"+vitri+"')";
            int x = state.executeUpdate(sql);
            if (x == 1) {
                flag = true;
            } else {
                flag = false;
            }
        } catch (Exception e) {
        }
        return flag;
    }

    @Override
    public boolean updatePhong(int idphong,String tenPhong, String vitri, int loaiphong) throws RemoteException {
        boolean flag = false;
        try {
            Statement state = conn().createStatement();
            String sql = "UPDATE `tbl_phong` SET `tenPhong`='"+tenPhong+"',`vitri`='"+vitri+"',`loaiPhong`="+loaiphong+" WHERE `idPhong`="+idphong;
            int x = state.executeUpdate(sql);
            if (x == 1) {
                flag = true;
            } else {
                flag = false;
            }
        } catch (Exception e) {
        }
        return flag;
    }

    @Override
    public boolean DeletePhong(int idPhong) throws RemoteException {
        boolean flag = false;
        try {
            Statement state = conn().createStatement();
            String sql = "UPDATE `tbl_phong` SET daxoa=1 WHERE `idPhong`="+idPhong;
            int x = state.executeUpdate(sql);
            if (x == 1) {
                flag = true;
            } else {
                flag = false;
            }
        } catch (Exception e) {
        }
        return flag;
    }

    @Override
    public Vector cbbphong(String loaiphong) throws RemoteException {
        Vector result = new Vector();
        try {
            Statement state = conn().createStatement();
            String sql = "SELECT idloaiphong,tenloai FROM tbl_loaiphong where tenloai='"+loaiphong+"'";
            java.sql.ResultSet x = state.executeQuery(sql);
            while (x.next()) {
                Loaiphong kh = new Loaiphong(x.getInt(1), x.getString(2));
                result.add(kh);     // nhét vào đối tượng Vector result = new Vector();      
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public TableModel TimPhong(String key) throws RemoteException {
        Statement state;
        TableModel tb = null;
        try {
            String sql = "SELECT idPhong AS 'Mã phòng',tenPhong AS 'Tên phòng',vitri AS '[Vị trí]' ,tenloai AS 'Loại phòng', dongia AS 'Giá phòng',dvt AS 'Đơn vị tính' FROM tbl_phong AS a,tbl_loaiphong AS b WHERE a.`loaiPhong`=b.`idLoaiphong` AND a.`daxoa`=0 AND b.`daxoa`=0 AND `tenPhong` LIKE '%"+key+"%'";
            state = conn().createStatement();
            rs = state.executeQuery(sql);
            tb = DbUtils.resultSetToTableModel(rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tb;
    }

    @Override
    public TableModel loadPhongCoKhach() throws RemoteException {
        TableModel tb = null;
        ResultSet rs;
        Statement state;
        try {
            String sql = "SELECT idPhong AS 'Mã phòng',tenPhong AS 'Tên phòng',vitri AS 'Vị trí' ,tenloai AS 'Loại phòng', dongia AS 'Giá phòng',dvt AS 'Đơn vị tính' FROM tbl_phong AS a,tbl_loaiphong AS b, tl_thuephong as thue WHERE a.`loaiPhong`=b.`idLoaiphong` AND a.`daxoa`=0 AND b.`daxoa`=0 and NOW() BETWEEN ngaytra AND thue.daxoa=0 AND ngaytra IS NOT NULL";
            state = conn().createStatement();
            rs = state.executeQuery(sql);
            tb = DbUtils.resultSetToTableModel(rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tb;
    }

    @Override
    public boolean traphong(int idphong) throws RemoteException {
        boolean flag = false;
        try {
            Statement state = conn().createStatement();
            String sql = "UPDATE tl_thuephong SET daxoa=1 WHERE `idPhong`="+idphong;
            String sql1="UPDATE tbl_sudung SET dathanhtoan=1 WHERE `idPhong`="+idphong;
            int x = state.executeUpdate(sql);
            int y=state.executeUpdate(sql1);
            if (x>0 && y>0) {
                flag = true;
            } else {
                flag = false;
            }
        } catch (Exception e) {
            flag=false;
        }
        return flag;
    }

    @Override
    public TableModel LoadDV() throws RemoteException {    
        Statement state;
        TableModel tb = null;
        try {
            String sql = "select idDichvu as 'Mã DV', tendichvu as 'Tên DV', dongia as 'Đơn Giá' from tbl_dichvu where daxoa=0 order by idDichvu desc";
            state = conn().createStatement();
            rs = state.executeQuery(sql);
            tb = DbUtils.resultSetToTableModel(rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tb;
    }

    @Override
    public boolean insertDichVu(String tendv, int dongia) throws RemoteException { 
        boolean flag = false;
        try {
            Statement state = conn().createStatement();
            String sql = "INSERT INTO tbl_dichvu(tendichvu,dongia) VALUES('"+tendv+"','"+dongia+"')";
            int x = state.executeUpdate(sql);
            if (x == 1) {
                flag = true;
            } else {
                flag = false;
            }
        } catch (Exception e) {
        }
        return flag;
    }

    @Override
    public boolean updateDichVu(int idDichVu, String tenDichVu, int dongia) throws RemoteException { 
        boolean flag = false;
        try {
            Statement state = conn().createStatement();
            String sql = "UPDATE tbl_dichvu set tendichvu='"+tenDichVu+"',dongia='"+dongia+"' where idDichvu='"+idDichVu+"'";
            int x = state.executeUpdate(sql);
            if (x == 1) {
                flag = true;
            } else {
                flag = false;
            }
        } catch (Exception e) {
        }
        return flag;
    }

    @Override
    public boolean DeleteDichVu(int idDichVu) throws RemoteException {       
        boolean flag = false;
        try {
            Statement state = conn().createStatement();
            String sql = "UPDATE tbl_dichvu set daxoa=1 where idDichvu='"+idDichVu+"'";
            int x = state.executeUpdate(sql);
            if (x == 1) {
                flag = true;
            } else {
                flag = false;
            }
        } catch (Exception e) {
        }
        return flag;
    }

    @Override
    public TableModel TimDichVu(String key) throws RemoteException {
        Statement state;
        TableModel tb = null;
        try {
            String sql = "select idDichvu as 'Mã DV', tendichvu as 'Tên DV', dongia as 'Đơn Giá' from tbl_dichvu where tendichvu LIKE '%"+key+"%' and daxoa=0 order by idDichvu desc";
            state = conn().createStatement();
            rs = state.executeQuery(sql);
            tb = DbUtils.resultSetToTableModel(rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tb;
    }

    @Override
    public TableModel LoadLoaiPhong() throws RemoteException {
        Statement state;
        TableModel tb = null;
        try {
            String sql = "select idLoaiphong as 'Mã', tenloai as 'Tên Loại', dongia as 'Đơn Giá' from tbl_loaiphong where daxoa=0 order by idLoaiphong desc";
            state = conn().createStatement();
            rs = state.executeQuery(sql);
            tb = DbUtils.resultSetToTableModel(rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tb;
    }

    @Override
    public boolean DeleteLoaiPhong(int idLoaiDV) throws RemoteException {
        boolean flag = false;
        try {
            Statement state = conn().createStatement();
            String sql = "UPDATE tbl_loaiphong set daxoa=1 where idLoaiphong='"+idLoaiDV+"'";
            int x = state.executeUpdate(sql);
            if (x == 1) {
                flag = true;
            } else {
                flag = false;
            }
        } catch (Exception e) {
        }
        return flag;
    }

    @Override
    public boolean insertLoaiPhong(String tenLoaiphong, int dongia) throws RemoteException {
        boolean flag = false;
        try {
            Statement state = conn().createStatement();
            String sql = "INSERT INTO tbl_loaiphong(tenloai,dongia) VALUES('"+tenLoaiphong+"','"+dongia+"')";
            int x = state.executeUpdate(sql);
            if (x == 1) {
                flag = true;
            } else {
                flag = false;
            }
        } catch (Exception e) {
        }
        return flag;
    }

    @Override
    public boolean updateLoaiPhong(int idLoai, String tenLoai, int dongia) throws RemoteException {
        boolean flag = false;
        try {
            Statement state = conn().createStatement();
            String sql = "UPDATE tbl_loaiphong set tenloai='"+tenLoai+"',dongia='"+dongia+"' where idLoaiphong='"+idLoai+"'";
            int x = state.executeUpdate(sql);
            if (x == 1) {
                flag = true;
            } else {
                flag = false;
            }
        } catch (Exception e) {
        }
        return flag;
    }

    @Override
    public boolean insertThietBi(String tenthietbi, String xuatxu) throws RemoteException {
        boolean flag = false;
        try {
            Statement state = conn().createStatement();
            String sql = "insert into tbl_thietbi(tenthietbi,xuatxu) values('"+tenthietbi+"','"+xuatxu+"')";
            int x = state.executeUpdate(sql);
            if (x == 1) {
                flag = true;
            } else {
                flag = false;
            }
        } catch (Exception e) {
        }
        return flag;
    }

    @Override
    public boolean updateThietBi(int idthietbi, String tenthietbi, String xuatxu) throws RemoteException {
        boolean flag = false;
        try {
            Statement state = conn().createStatement();
            String sql = "UPDATE tbl_thietbi set tenthietbi='"+tenthietbi+"',xuatxu='"+xuatxu+"' where idThietbi='"+idthietbi+"'";
            int x = state.executeUpdate(sql);
            if (x == 1) {
                flag = true;
            } else {
                flag = false;
            }
        } catch (Exception e) {
        }
        return flag;
    }

    @Override
    public boolean DeleteThietBi(int idThietBi) throws RemoteException {
        boolean flag = false;
        try {
            Statement state = conn().createStatement();
            String sql = "UPDATE tbl_thietbi set daxoa=1 where idThietbi='"+idThietBi+"'";
            int x = state.executeUpdate(sql);
            if (x == 1) {
                flag = true;
            } else {
                flag = false;
            }
        } catch (Exception e) {
        }
        return flag;
    }

    @Override
    public TableModel LoadThietBi() throws RemoteException {
        Statement state;
        TableModel tb = null;
        try {
            String sql = "select idThietbi as 'Mã TB', tenthietbi as 'Tên Thiết Bị', xuatxu as 'Xuất Xứ' from tbl_thietbi where daxoa=0 order by idThietbi desc";
            state = conn().createStatement();
            rs = state.executeQuery(sql);
            tb = DbUtils.resultSetToTableModel(rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tb;
    }

    @Override
    public TableModel TimThietBi(String key) throws RemoteException {
        Statement state;
        TableModel tb = null;
        try {
            String sql = "select idThietbi as 'Mã TB', tenthietbi as 'Tên Thiết Bị', xuatxu as 'Xuất Xứ' from tbl_thietbi where daxoa=0 and tenthietbi LIKE '%"+key+"%' order by idThietbi desc";
            state = conn().createStatement();
            rs = state.executeQuery(sql);
            tb = DbUtils.resultSetToTableModel(rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tb;
    }

    @Override
    public TableModel LoadDanhSachPhongDaThue() throws RemoteException {  
        Statement state;
        TableModel tb = null;
        try {
            String sql = "SELECT tenphong FROM tbl_phong phong LEFT JOIN `tl_thuephong`  thue ON phong.`idPhong`=thue.`idPhong` WHERE NOW() BETWEEN ngaynhan AND ngaytra AND thue.daxoa=0 AND ngaytra IS NOT NULL";
            state = conn().createStatement();
            rs = state.executeQuery(sql);
            tb = DbUtils.resultSetToTableModel(rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tb;
    }

    @Override
    public TableModel LoadThongTinPhong(String phong) throws RemoteException {
        TableModel tb = null;
        ResultSet rs;
        Statement state;
        try {
            String sql = "SELECT idPhong AS 'Mã phòng',tenPhong AS 'Tên phòng',vitri AS '[Vị trí]' ,tenloai AS 'Loại phòng', dongia AS 'Giá phòng',dvt AS 'Đơn vị tính' FROM tbl_phong AS a,tbl_loaiphong AS b WHERE a.`loaiPhong`=b.`idLoaiphong` AND a.`daxoa`=0 AND b.`daxoa`=0 and tenphong='"+phong+"'";
            state = conn().createStatement();
            rs = state.executeQuery(sql);
            tb = DbUtils.resultSetToTableModel(rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tb;
    }     

    @Override
    public TableModel LoadThuePhong() throws RemoteException {
        TableModel tb = null;
        ResultSet rs;
        Statement state;
        try {
            String sql = "SELECT tenphong,kh.hoten,ngaydat,ngaytra,nv.hoten FROM tl_thuephong thue,tbl_phong phong,tbl_khachhang kh, tbl_nhanvien nv WHERE phong.idPhong=thue.idPhong AND thue.idKhachhang=kh.idKhachhang AND thue.idNV=nv.idNV and thue.daxoa=0";
            state = conn().createStatement();
            rs = state.executeQuery(sql);
            tb = DbUtils.resultSetToTableModel(rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tb;
    }

    @Override
    public Vector Loadcbbphong() throws RemoteException {  
        Vector result = new Vector();
        try {
            Statement state = conn().createStatement();
            String sql = "SELECT phong.idPhong,tenphong FROM tbl_phong phong LEFT JOIN `tl_thuephong`  thue ON phong.`idPhong`=thue.`idPhong` WHERE NOW() BETWEEN ngaynhan AND ngaytra AND thue.daxoa=0 AND thue.`idPhong` AND  ngaytra IS NOT NULL";
            java.sql.ResultSet x = state.executeQuery(sql);
            while (x.next()) {
                Phong p= new Phong(x.getInt(1), x.getString(2));
                result.add(p);    
            }
        } catch (Exception e) {

            e.printStackTrace();
        }
        return result;  
    }

    @Override
    public TableModel ThongKeDichVu(int dichvu) throws RemoteException {
        TableModel tb = null;
        ResultSet rs;
        Statement state;
        try {
            String sql = "SELECT tendichvu AS 'Ten Dich Vu', ngaysudung AS 'Ngay Su Dung',soluong AS 'So Luong',dongia AS 'Don Gia', (soluong*dongia) AS 'Thanh Tien' FROM tbl_sudung sd, tbl_dichvu dv WHERE sd.idDichvu=dv.idDichvu AND sd.idDichvu='"+dichvu+"'  AND sd.daxoa=0 AND dv.daxoa=0";
            state = conn().createStatement();
            rs = state.executeQuery(sql);
            tb = DbUtils.resultSetToTableModel(rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tb;
        
    }

    @Override
    public TableModel ThongKeDichVu(String tungay, String denngay) throws RemoteException {
        TableModel tb = null;
        ResultSet rs;
        Statement state;
        try {
            String sql = "SELECT tendichvu as 'Ten Dich Vu',ngaysudung AS 'Ngày Sử Dụng',soluong AS 'Số Lượng',dongia AS 'Đơn Giá', (soluong*dongia) AS 'Thành Tiền' FROM tbl_sudung sd, tbl_dichvu dv WHERE sd.idDichvu=dv.idDichvu and ngaysudung BETWEEN '"+tungay+"' AND '"+denngay+"' AND sd.daxoa=0 AND dv.daxoa=0";
            state = conn().createStatement();
            rs = state.executeQuery(sql);
            tb = DbUtils.resultSetToTableModel(rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tb;
    }

    @Override
    public TableModel ThongKeDichVu(String tungay, String denngay, int dichvu) throws RemoteException {
        TableModel tb = null;
        ResultSet rs;
        Statement state;
        try {
            String sql = "SELECT tendichvu as 'Ten Dich Vu',ngaysudung AS 'Ngày Sử Dụng',soluong AS 'Số Lượng',dongia AS 'Đơn Giá', (soluong*dongia) AS 'Thành Tiền' FROM tbl_sudung sd, tbl_dichvu dv WHERE sd.idDichvu=dv.idDichvu and ngaysudung BETWEEN '"+tungay+"' AND '"+denngay+"' AND dv.idDichvu='"+dichvu+"' AND sd.daxoa=0 AND dv.daxoa=0";
            state = conn().createStatement();
            rs = state.executeQuery(sql);
            tb = DbUtils.resultSetToTableModel(rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tb;
    }

    @Override
    public TableModel ThongKeTienDichVu(String tungay, String denngay, int dichvu) throws RemoteException {
         TableModel tb = null;
        ResultSet rs;
        Statement state;
        try {
            String sql = "SELECT SUM(dongia*soluong) FROM tbl_sudung sd, tbl_dichvu dv WHERE sd.idDichvu=dv.idDichvu and ngaysudung BETWEEN '"+tungay+"' AND '"+denngay+"' and dv.idDichvu='"+dichvu+"' and sd.daxoa=0 AND dv.daxoa=0";
            state = conn().createStatement();
            rs = state.executeQuery(sql);
            tb = DbUtils.resultSetToTableModel(rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tb;
    }

    @Override
    public TableModel ThongKeTienDichVu(int dichvu) throws RemoteException {
        TableModel tb = null;
        ResultSet rs;
        Statement state;
        try {
            String sql = "SELECT SUM(dongia*soluong) FROM tbl_sudung sd, tbl_dichvu dv WHERE sd.idDichvu=dv.idDichvu and dv.idDichvu='"+dichvu+"' AND sd.daxoa=0 AND dv.daxoa=0";
            state = conn().createStatement();
            rs = state.executeQuery(sql);
            tb = DbUtils.resultSetToTableModel(rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tb;
    }

    @Override
    public TableModel ThongKeTienDichVu(String tungay, String denngay) throws RemoteException {
        TableModel tb = null;
        ResultSet rs;
        Statement state;
        try {
            String sql = "SELECT SUM(dongia*soluong) FROM tbl_sudung sd, tbl_dichvu dv WHERE sd.idDichvu=dv.idDichvu and ngaysudung BETWEEN '"+tungay+"' AND '"+denngay+"' AND sd.daxoa=0 AND dv.daxoa=0";
            state = conn().createStatement();
            rs = state.executeQuery(sql);
            tb = DbUtils.resultSetToTableModel(rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tb;
    }

    @Override
    public Vector loadcobNhanVien(String nhanvien) throws RemoteException {
        Vector result = new Vector();
        try {
            Statement state = conn().createStatement();
            String sql = "SELECT idNV,hoten FROM tbl_nhanvien where idNV="+nhanvien+"";
            java.sql.ResultSet x = state.executeQuery(sql);
            while (x.next()) {
                NhanVien kh = new NhanVien(x.getInt(1), x.getString(2));
                result.add(kh);     // nhét vào đối tượng Vector result = new Vector();      
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public TableModel KiemTraThemTaiKhoan(int manhanvien) throws RemoteException {
        TableModel tb = null;
        ResultSet rs;
        Statement state;
        try {
            String sql = "SELECT idNV FROM tbl_taikhoan WHERE idNV='"+manhanvien+"'";
            state = conn().createStatement();
            rs = state.executeQuery(sql);
            tb = DbUtils.resultSetToTableModel(rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tb;
    }

    @Override
    public TableModel KiemTraUsername(String user) throws RemoteException {
        TableModel tb = null;
        ResultSet rs;
        Statement state;
        try {
            String sql = "SELECT username FROM tbl_taikhoan WHERE username='"+user+"'";
            state = conn().createStatement();
            rs = state.executeQuery(sql);
            tb = DbUtils.resultSetToTableModel(rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tb;
    }

    @Override
    public String thongtindb() throws RemoteException {
        String thongtin=databaseinfo.getDb()+"-"+databaseinfo.getIp()+"-"+databaseinfo.getPass()+"-"+databaseinfo.getUser()+"-"+databaseinfo.getPort();
        return thongtin;
    }
}
